
//package de.htwb.rpl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import de.htwb.rpl.exception.DataException;
import de.htwb.rpl.exception.ServiceException;
import de.htwb.rpl.service.IRaumaustauschService;
import de.htwb.utils.JdbcUtils;

public class RaumaustauschService implements IRaumaustauschService {
	private static final Logger L = LoggerFactory.getLogger(RaumaustauschService.class);
	Connection connection = null;

	@Override
	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	protected Connection getConnection() {
		if (connection == null) {
			throw new DataException("Connection wurde nicht gesetzt");
		}
		return connection;
	}

	@Override
	public void tauscheRaum(int ridAlt, int ridNeu) {
		L.info("ridAlt: " + ridAlt + ", ridNeu: " + ridNeu);
		if (!istRaumExistent(ridNeu)) {
			throw new ServiceException("");
		}
		if (!istRaumExistent(ridAlt)) {
			throw new ServiceException("");
		}
		if (!pruefAusstattung(ridAlt, ridNeu)) {
			throw new ServiceException("");
		}
		if (!pruefBelegung(ridAlt, ridNeu)) {
			throw new ServiceException("");
		} 
		if (!uebertrageBelegung(ridAlt, ridNeu)) {
			throw new ServiceException("");
		} 
	}

	public boolean istRaumExistent(int rid) {
		L.info("istRaumExistent aufgerufen! " + rid);
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = getConnection().prepareStatement(
					"SELECT * " + "FROM Raum " + "WHERE RId = ?");
			statement.setInt(1, rid);
			result = statement.executeQuery();
			return result.next();
		} catch (SQLException e) {
			L.error("Fehler aufgetreten!", e);
			throw new DataException("Fehler in istRaumExistent!");
		} finally {
			JdbcUtils.closeResultSet(result);
			JdbcUtils.closeStatement(statement);
		}
	}

	public boolean pruefAusstattung(int ridAlt, int ridNeu) {
		L.info("pruefAusstattung aufgerufen! " + ridAlt + " " + ridNeu);
		PreparedStatement statement = null;
		ResultSet resultAnzahl = null;
		ResultSet resultAlt = null;
		ResultSet resultNeu = null;
		boolean pruef = true;
		try {
			statement = getConnection().prepareStatement(
					"SELECT MAX(MID) as MAXIMUM " + 
					"FROM Ausstattung ");
			resultAnzahl = statement.executeQuery();
			resultAnzahl.next();
			for (int count = 1; count <= resultAnzahl.getInt("MAXIMUM"); count++) {
				statement = getConnection().prepareStatement(
						"SELECT Anzahl " + 
						"FROM Ausstattung "	+ 
						"WHERE RId = ? " + 
						"AND MId = ? ");
				statement.setInt(1, ridAlt);
				statement.setInt(2, count);
				resultAlt = statement.executeQuery();
				if (resultAlt.next() && pruef) {
					statement = getConnection().prepareStatement(
							"SELECT Anzahl " + 
							"FROM Ausstattung " + 
							"WHERE RId = ? " + 
							"AND MId = ? ");
					statement.setInt(1, ridNeu);
					statement.setInt(2, count);
					resultNeu = statement.executeQuery();
					if (resultNeu.next() && resultAlt.getInt("Anzahl") <= resultNeu.getInt("Anzahl")) {
						pruef = true;
					} else {
						pruef = false;
					}
				}

			}
			return pruef;
		} catch (SQLException e) {
			L.error("Fehler aufgetreten!", e);
			throw new DataException("Fehler in pruefAusstattung!");
		} finally {
			JdbcUtils.closeResultSet(resultAnzahl);
			JdbcUtils.closeResultSet(resultAlt);
			JdbcUtils.closeResultSet(resultNeu);
			JdbcUtils.closeStatement(statement);
		}
	}

	public boolean pruefBelegung(int ridAlt, int ridNeu) {
		L.info("pruefBelegung aufgerufen! " + ridAlt + " " + ridNeu);
		PreparedStatement statement = null;
		ResultSet resultAlt = null;
		ResultSet resultNeu = null;
		boolean pruef = true;
		try {
			statement = getConnection().prepareStatement(
				"SELECT Tag, Block " + 
				"FROM Termin t " + 
				"INNER JOIN Belegung b ON t.tid = b.tid " + 
				"WHERE b.RId = ? ");
			statement.setInt(1, ridAlt);
			resultAlt = statement.executeQuery();
			
			while(resultAlt.next()){
				statement = getConnection().prepareStatement(
					"SELECT Tag, Block " + 
					"FROM Termin t " + 
					"INNER JOIN Belegung b ON t.tid = b.tid " + 
					"WHERE b.RId = ? " + 
					"AND t.Tag = ? " + 
					"AND t.Block = ? ");
				statement.setInt(1, ridNeu);
				statement.setString(2, resultAlt.getString("Tag"));
				statement.setString(3, resultAlt.getString("Block"));
				resultNeu = statement.executeQuery();
				if(!resultNeu.next() && pruef){
					
				} else {
					pruef = false;
				}
			}
			return pruef;
		} catch (SQLException e) {
			L.error("Fehler aufgetreten!", e);
			throw new DataException("Fehler in pruefBelegung!");
		} finally {
			JdbcUtils.closeResultSet(resultAlt);
			JdbcUtils.closeResultSet(resultNeu);
			JdbcUtils.closeStatement(statement);
		}
	}

	public boolean uebertrageBelegung(int ridAlt, int ridNeu){
		L.info("uebertrageBelegung aufgerufen! " + ridAlt + " " + ridNeu);
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = getConnection().prepareStatement(
				"UPDATE Belegung " +
				"SET RID = ?" + 
				"WHERE RId = ? ");
			statement.setInt(1, ridNeu);
			statement.setInt(2, ridAlt);
			result = statement.executeQuery();
			return result.next();
		} catch (SQLException e) {
			L.error("Fehler aufgetreten!", e);
			throw new DataException("Fehler in uebertrageBelegung!");
		} finally {
			JdbcUtils.closeResultSet(result);
			JdbcUtils.closeStatement(statement);
		}
	}
}
